import requests
import json
import mysql.connector

url = 'https://datacenter-web.eastmoney.com/api/data/v1/get?callback=jQuery1123002799173512530473_1710849042406&sortColumns=TRADE_DATE&sortTypes=-1&pageSize=50&pageNumber=1&reportName=PRT_BLOCKTRADE_MARKET_STA&columns=TRADE_DATE%2CSZ_INDEX%2CSZ_CHANGE_RATE%2CBLOCKTRADE_DEAL_AMT%2CPREMIUM_DEAL_AMT%2CPREMIUM_RATIO%2CDISCOUNT_DEAL_AMT%2CDISCOUNT_RATIO&source=WEB&client=WEB'

response = requests.get(url)

# 创建数据库连接
cnx = mysql.connector.connect(user='root', password='123456',
                              host='localhost',
                              database='eastmoney')
cursor = cnx.cursor()

# 检查响应状态码
if response.status_code == 200:
    # 去除jQuery的回调函数部分，获取真正的JSON数据
    json_start_index = response.text.find('{')
    json_end_index = response.text.rfind('}') + 1
    data_string = response.text[json_start_index:json_end_index]

    # 解析JSON字符串
    data_dict = json.loads(data_string)
    result_data = data_dict['result']['data']

    # 遍历数据并插入到数据库中
    for item in result_data:
        TRADE_DATE = item['TRADE_DATE']
        SZ_INDEX = float(item['SZ_INDEX'])  # 转换为浮点数
        SZ_CHANGE_RATE = float(item['SZ_CHANGE_RATE'])  # 转换为浮点数
        BLOCKTRADE_DEAL_AMT = item['BLOCKTRADE_DEAL_AMT']
        PREMIUM_DEAL_AMT = item['PREMIUM_DEAL_AMT']
        PREMIUM_RATIO = item['PREMIUM_RATIO']
        DISCOUNT_DEAL_AMT = item['DISCOUNT_DEAL_AMT']
        DISCOUNT_RATIO = item['DISCOUNT_RATIO']

        # 插入数据到数据库，不需要指定id，因为它是自增的
        add_count = ("INSERT INTO count (TRADE_DATE, SZ_INDEX, SZ_CHANGE_RATE, BLOCKTRADE_DEAL_AMT, "
                     "PREMIUM_DEAL_AMT, PREMIUM_RATIO, DISCOUNT_DEAL_AMT, DISCOUNT_RATIO) "
                     "VALUES (%s, %s, %s, %s, %s, %s, %s, %s)")
        cursor.execute(add_count, (TRADE_DATE, SZ_INDEX, SZ_CHANGE_RATE, BLOCKTRADE_DEAL_AMT,
                                   PREMIUM_DEAL_AMT, PREMIUM_RATIO, DISCOUNT_DEAL_AMT, DISCOUNT_RATIO))
        # 提交事务并关闭连接
    cnx.commit()
    cursor.close()
    cnx.close()
else:
    print(f'Request failed with status code {response.status_code}')